package edu.tjhsst.androidIntranet.db;

import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import edu.tjhsst.androidIntranet.news.NewsEntry;

public class IntranetDbAdapter {

	private static final String DATABASE_NAME = "androidIntranet";
	private static final int DATABASE_VERSION = 26;

	private final Context mContext;

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private static IntranetDbAdapter singleton;
	private int databaseRefs;

	private AccountDbAdapter accountDbAdapter;
	private NewsDbAdapter newsDbAdapter;
	private EighthDbAdapter eighthDbAdapter;

	public IntranetDbAdapter(Context context) {
		//prevents leaks of Activities
		context=context.getApplicationContext();
		this.mContext = context;
		accountDbAdapter = new AccountDbAdapter();
		newsDbAdapter = new NewsDbAdapter();
		eighthDbAdapter = new EighthDbAdapter();
	}

	private IntranetDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mContext);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	private void close() {
		Log.d("IntranetDbAdapter", "Closing the entire database");
		mDbHelper.close();
		singleton=null;
	}

	public static AccountDbAdapter getAccountDbAdapter(Context context) {
		if(singleton==null) {
			Log.d("IntranetDbAdapter", "Creating and opening a new singleton database instance for the Account");
			singleton = new IntranetDbAdapter(context).open();
		}
		singleton.databaseRefs++;
		return singleton.accountDbAdapter;
	}

	public static NewsDbAdapter getNewsDbAdapter(Context context) {
		if(singleton==null) {
			Log.d("IntranetDbAdapter", "Creating and opening a new singleton database instance for the News");
			singleton = new IntranetDbAdapter(context).open();
		}
		singleton.databaseRefs++;
		return singleton.newsDbAdapter;
	}

	public static EighthDbAdapter getEighthDbAdapter(Context context) {
		if(singleton==null) {
			Log.d("IntranetDbAdapter", "Creating and opening a new singleton database instance for Eighth");
			singleton = new IntranetDbAdapter(context).open();
		}
		singleton.databaseRefs++;
		return singleton.eighthDbAdapter;
	}

	public class AccountDbAdapter {

		public static final String KEY_REMEMBER = "remember";
		public static final String KEY_USERNAME = "username";
		public static final String KEY_PASSWORD = "password";

		private static final String DATABASE_TABLE = "account";
		private static final String KEY_AUTO = "auto";

		private static final String DATABASE_CREATE =
			"create table " + DATABASE_TABLE + " (" + KEY_REMEMBER + " integer, " + KEY_USERNAME + " text primary key, "
			+ KEY_PASSWORD + " text, " + KEY_AUTO + " integer);";

		public boolean fetchRemember() {
			Cursor c = mDb.query(AccountDbAdapter.DATABASE_TABLE, new String[] {AccountDbAdapter.KEY_REMEMBER}, null, null, null, null, null);
			c.moveToFirst();
			return (c.getInt(0)!=0);
		}

		public void setRemember(boolean remember) {
			//mDb.execSQL("update " + AccountDbAdapter.DATABASE_TABLE + " set " + AccountDbAdapter.KEY_REMEMBER + "=" + remember + ";");
			ContentValues pairs = new ContentValues(1);
			pairs.put(AccountDbAdapter.KEY_REMEMBER, remember?1:0);
			mDb.update(AccountDbAdapter.DATABASE_TABLE, pairs, null, null);
		}

		public String fetchUsername() {
			Cursor c = mDb.query(AccountDbAdapter.DATABASE_TABLE, new String[] {AccountDbAdapter.KEY_USERNAME}, null, null, null, null, null);
			c.moveToFirst();
			return c.getString(0);
		}

		public void setUsername(String username) {
			//mDb.execSQL("update " + AccountDbAdapter.DATABASE_TABLE + " set " + AccountDbAdapter.KEY_USERNAME + "='" + username + "';");
			ContentValues pairs = new ContentValues(1);
			pairs.put(AccountDbAdapter.KEY_USERNAME, username);
			mDb.update(AccountDbAdapter.DATABASE_TABLE, pairs, null, null);
		}

		public String fetchPassword() {
			Cursor c = mDb.query(AccountDbAdapter.DATABASE_TABLE, new String[] {AccountDbAdapter.KEY_PASSWORD}, null, null, null, null, null);
			c.moveToFirst();
			return c.getString(0);
		}

		public void setPassword(String password) {
			//mDb.execSQL("update " + AccountDbAdapter.DATABASE_TABLE + " set " + AccountDbAdapter.KEY_PASSWORD + "='" + password + "';");
			ContentValues pairs = new ContentValues(1);
			pairs.put(AccountDbAdapter.KEY_PASSWORD, password);
			mDb.update(AccountDbAdapter.DATABASE_TABLE, pairs, null, null);
		}

		public void close() {
			databaseRefs--;
			if(databaseRefs==0) {
				IntranetDbAdapter.this.close();
			}
		}

		public boolean fetchAutoLogin() {
			Cursor c = mDb.query(AccountDbAdapter.DATABASE_TABLE, new String[] {AccountDbAdapter.KEY_AUTO}, null, null, null, null, null);
			c.moveToFirst();
			return (c.getInt(0)!=0);
		}

		public void setAutoLogin(boolean auto) {
			//mDb.execSQL("update " + AccountDbAdapter.DATABASE_TABLE + " set " + AccountDbAdapter.KEY_REMEMBER + "=" + remember + ";");
			ContentValues pairs = new ContentValues(1);
			pairs.put(AccountDbAdapter.KEY_AUTO, auto?1:0);
			mDb.update(AccountDbAdapter.DATABASE_TABLE, pairs, null, null);
		}
	}

	public class EighthDbAdapter {

		public static final String KEY_DATE = "date";
		public static final String KEY_BLOCK = "block";
		public static final String KEY_SELECTED_ACTIVITY = "selectedActivity";
		public static final String KEY_TEACHER = "teacher";
		public static final String KEY_ROOM = "room";

		private static final String DATABASE_TABLE = "eighth";

		private static final String DATABASE_CREATE =
			"create table " + DATABASE_TABLE + " ("
			+ KEY_DATE +" long, "
			+ KEY_BLOCK + " text, "
			+ KEY_SELECTED_ACTIVITY + " text, "
			+ KEY_TEACHER + " text, "
			+ KEY_ROOM + " text);";

		public Cursor getEighthCursor() {
			return mDb.query(EighthDbAdapter.DATABASE_TABLE, new String[] {KEY_DATE, KEY_BLOCK, KEY_SELECTED_ACTIVITY, KEY_TEACHER, KEY_ROOM}, null, null, null, null, EighthDbAdapter.KEY_DATE+" DESC, " + EighthDbAdapter.KEY_BLOCK + " DESC");
		}

		public Cursor getEighthCursor(Long startDate, Long endDate) {
			return mDb.query(EighthDbAdapter.DATABASE_TABLE, new String[] {KEY_DATE, KEY_BLOCK, KEY_SELECTED_ACTIVITY, KEY_TEACHER, KEY_ROOM}, KEY_DATE + " between " + startDate + " and " + endDate, null, null, null, EighthDbAdapter.KEY_DATE+" DESC, " + EighthDbAdapter.KEY_BLOCK + " DESC");			
		}

		/**
		 * Returns whether or not the block already existed in the database
		 * @param date
		 * @param block
		 * @param selectedActivity
		 * @param teacher
		 * @param room
		 * @return
		 */
		public boolean insertOrUpdateBlock(Long date, String block, String selectedActivity, String teacher, String room) {
			if(mDb.query(DATABASE_TABLE, new String[] {KEY_DATE, KEY_BLOCK}, KEY_DATE+"="+date+" and "+KEY_BLOCK+"=\'"+block+"\'", null, null, null, null).getCount()>0) {
				ContentValues data = new ContentValues();
				data.put(KEY_SELECTED_ACTIVITY, selectedActivity);
				data.put(KEY_TEACHER, teacher);
				data.put(KEY_ROOM, room);
				mDb.update(DATABASE_TABLE, data, KEY_DATE+"="+date+" and "+KEY_BLOCK+"=\'"+block+"\'", null);
				return true;
			} else {
				ContentValues data = new ContentValues();
				data.put(KEY_DATE, date);
				data.put(KEY_BLOCK, block);
				data.put(KEY_SELECTED_ACTIVITY, selectedActivity);
				data.put(KEY_TEACHER, teacher);
				data.put(KEY_ROOM, room);
				mDb.insert(DATABASE_TABLE, null, data);
				return false;
			}
		}

		public void close() {
			databaseRefs--;
			if(databaseRefs==0) {
				IntranetDbAdapter.this.close();
			}
		}
	}

	public class NewsDbAdapter {

		public static final String KEY_IDENTIFIER = "identifier";
		public static final String KEY_TITLE = "title";
		public static final String KEY_CONTENT = "content";
		private static final String KEY_POSTER = "poster";
		public static final String KEY_DATE = "date";
		public static final String KEY_READ = "read";

		public static final String DATABASE_TABLE = "news";

		public static final String DATABASE_CREATE =
			"create table " + DATABASE_TABLE + " (" + KEY_IDENTIFIER + " integer primary key, "
			+ KEY_TITLE + " text, "
			+ KEY_CONTENT + " text, "
			+ KEY_POSTER + " text, "
			+ KEY_DATE + " long, "
			+ KEY_READ + " integer);";

		public Cursor getNewsCursor() {
			return mDb.query(NewsDbAdapter.DATABASE_TABLE, new String[] {NewsDbAdapter.KEY_IDENTIFIER, NewsDbAdapter.KEY_TITLE, NewsDbAdapter.KEY_CONTENT, NewsDbAdapter.KEY_POSTER, NewsDbAdapter.KEY_DATE, NewsDbAdapter.KEY_READ}, null, null, null, null, NewsDbAdapter.KEY_DATE+" DESC");
		}

		public void insertNews(int identifier, String title, String content, String poster, Long datePosted, boolean read) {
			Log.d("NewsDbAdapter", "Inserting "+title+": "+content+" which was posted at "+datePosted+" and has a read value of "+read);
			ContentValues data = new ContentValues(3);
			data.put(NewsDbAdapter.KEY_IDENTIFIER, identifier);
			data.put(NewsDbAdapter.KEY_TITLE, title);
			data.put(NewsDbAdapter.KEY_CONTENT, content);
			data.put(NewsDbAdapter.KEY_POSTER, poster);
			data.put(NewsDbAdapter.KEY_DATE, datePosted);
			data.put(NewsDbAdapter.KEY_READ, read?1:0);
			mDb.insert(NewsDbAdapter.DATABASE_TABLE, null, data);
		}

		public void close() {
			databaseRefs--;
			if(databaseRefs==0) {
				IntranetDbAdapter.this.close();
			}
		}

		public NewsEntry getNewsEntry(int identifier) {
			Cursor c = mDb.query(NewsDbAdapter.DATABASE_TABLE, new String[] {NewsDbAdapter.KEY_IDENTIFIER, NewsDbAdapter.KEY_TITLE, NewsDbAdapter.KEY_CONTENT, NewsDbAdapter.KEY_POSTER, NewsDbAdapter.KEY_DATE, NewsDbAdapter.KEY_READ}, NewsDbAdapter.KEY_IDENTIFIER+"="+identifier, null, null, null, null);
			c.moveToFirst();
			return new NewsEntry(c.getInt(0), c.getString(1), c.getString(2), c.getString(3), new Date(c.getLong(4)), c.getInt(5)==1);
		}

		public boolean containsEntry(int identifier) {
			if(mDb.query(NewsDbAdapter.DATABASE_TABLE, new String[] {NewsDbAdapter.KEY_IDENTIFIER}, NewsDbAdapter.KEY_IDENTIFIER+"="+identifier, null, null, null, null).getCount()>0) {
				return true;
			} else {
				return false;
			}
		}
	}

	public class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(AccountDbAdapter.DATABASE_CREATE);
			if(db.query(AccountDbAdapter.DATABASE_TABLE, new String[] {AccountDbAdapter.KEY_USERNAME}, null, null, null, null, null).getCount()==0) {
				emptyAccountData(db);
			} else {
				Log.d("AccountDbAdapter", "Records exist");
			}
			db.execSQL(NewsDbAdapter.DATABASE_CREATE);
			db.execSQL(EighthDbAdapter.DATABASE_CREATE);
		}

		private void emptyAccountData(SQLiteDatabase db) {
			ContentValues norms = new ContentValues(3);
			norms.put(AccountDbAdapter.KEY_REMEMBER, 0);
			norms.put(AccountDbAdapter.KEY_USERNAME, "");
			norms.put(AccountDbAdapter.KEY_PASSWORD, "");
			norms.put(AccountDbAdapter.KEY_AUTO, 0);
			db.insert(AccountDbAdapter.DATABASE_TABLE, null, norms);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			//db.execSQL("drop table "+ NewsDbAdapter.DATABASE_TABLE);
			//db.execSQL(NewsDbAdapter.DATABASE_CREATE);
			db.execSQL("drop table " + AccountDbAdapter.DATABASE_TABLE);
			db.execSQL(AccountDbAdapter.DATABASE_CREATE);
			emptyAccountData(db);
			//db.execSQL(EighthDbAdapter.DATABASE_CREATE);
		}

	}
}
